package cc.zy.base.businesses.utils;

import cc.zy.base.businesses.entity.EntranceScore;
import cc.zy.base.businesses.entity.ErrorLog;
import cc.zy.base.businesses.service.IEntranceScoreService;
import cc.zy.base.common.entity.FebsResponse;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * @author wangpin
 * @date 2021/02/01
 * 读取Excel文件数据
 *
 */
public class ReadTermExcel {
    private static String REMARK="入学成绩导入";
    public static FebsResponse readData(IEntranceScoreService iEntranceScoreService) throws NoSuchFieldException, IllegalAccessException, IOException {
        File xlsFile = new File("H:/775.xlsx");
        List<EntranceScore> entranceScores = new ArrayList<>();
        List<ErrorLog> errorLogs = new ArrayList<>();
        List<EntranceScore> dataTable = new ArrayList<>();
        Map<String, Object> titleMap = new HashMap<>();
//        InputStream inputStream = multipartFile.getInputStream();
//        System.out.println(multipartFile.getName());
        // 工作表
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(xlsFile);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        // 遍历表。
        for (int i1 = 0; i1 < numberOfSheets; i1++) {

            Sheet sheet = workbook.getSheetAt(i1);
            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }
            // 读数据。
            //4、循环读取表格数据
            for (Row row : sheet) {
                int columNos = row.getLastCellNum();// 表头总共的列数
                //目前读取的行数
                int rowNum = row.getRowNum();
                //首行（即表头）不读取
                if (rowNum == 0) {
                    for (int i = 0; i <= columNos; i++) {
                        Cell cell = row.getCell(i);
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                            titleMap.put(cell.getStringCellValue(),i);
                        }
                    }
                    continue;
                }
                for (Cell cell : row) {
                    cell.setCellType(CellType.STRING);
                }
                EntranceScore score = new EntranceScore();
                //读取当前行中单元格数据，索引从0开始
                /**
                 * 获取模板中的学生基本信息
                 */
                Integer batchNameIndex=(Integer) titleMap.get("批次");
                String batchName=null;
                Cell batchNameCell = row.getCell(batchNameIndex);
                if (batchNameCell==null||batchNameCell.equals("")){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(batchNameIndex+1);
                    errorLog.setRemark(REMARK);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("批次信息为空！");
                    errorLogs.add(errorLog);
                }else {
                    batchName = batchNameCell.getStringCellValue();
                    System.out.println(batchName);
                    score.setBatchName(batchName);
                }
                Integer identityIndex=(Integer) titleMap.get("身份证");
                Cell identityCell = row.getCell(identityIndex);
                String identity=null;
                if (identityCell==null||identityCell.equals("")){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(identityIndex+1);
                    errorLog.setRemark(REMARK);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("身份证号为空！");
                    errorLogs.add(errorLog);
                }else {
                    identity = identityCell.getStringCellValue();
                    score.setStuName(identity);
                }
                Integer studtyTypeIndex=(Integer) titleMap.get("类别");
                Cell studtyTypeCell = row.getCell(studtyTypeIndex);
                String studtyType=null;
                if (studtyTypeCell==null||studtyTypeCell.equals("")){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(studtyTypeIndex+1);
                    errorLog.setRemark(REMARK);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("学习形式为空！");
                    errorLogs.add(errorLog);
                }else {
                    studtyType = identityCell.getStringCellValue();
                    score.setIdentity(studtyType);
                }
                //通过联合主键查询表中所需的关联id

                Integer termNameIndex=(Integer) titleMap.get("学期");
                Cell termNameCell = row.getCell(termNameIndex);
                String termName=null;
                if (termNameCell==null||termNameCell.equals("")){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(termNameIndex+1);
                    errorLog.setRemark(REMARK);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("学期信息为空！");
                    errorLogs.add(errorLog);
                }else {
                    termName = termNameCell.getStringCellValue();
                    score.setCollegeName(termName);
                }

                entranceScores.add(score);
            }
            //5、关闭流
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //判断是否有错误
        if (errorLogs==null||errorLogs.isEmpty()){
            return new FebsResponse().message("200").data(dataTable);
        }else {
            return new FebsResponse().message("400").data(errorLogs);
        }
    }
}
